package org.com.coolfish.common.database.repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.com.coolfish.common.database.entity.KuyuAddPackage;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface KuyuAddPackageRepository extends JpaRepository<KuyuAddPackage, Integer> {
    /*
     * 需要刷新沉默期的套餐
     */
    @Query("from KuyuAddPackage e where e.cardId =:cardId")
    List<KuyuAddPackage> findFlashObject(@Param("cardId") Integer cardId);

    /*
     * 刷新沉默期套餐开始时间和结束时间
     * 
     */
    @Modifying
    @Query("update KuyuAddPackage e set e.starttime = :starttime , e.endtime = :endtime where e.id=:id")
    void flashSlientTime(@Param("starttime") Date starttime, @Param("endtime") Date endtime, @Param("id") Integer id);

    @Query("select new KuyuAddPackage (e.id,e.card,e.addtime,e.starttime,e.endtime,e.cardId) from KuyuAddPackage e WHERE e.uid = 50 AND e.endtime >now() AND e.status =2 AND e.type in (1,3) and e.starttime < now()")
    List<KuyuAddPackage> findMonthlyHaveSumFlow();

    /*
     * 查找当月套餐的总流量
     */
    @Query("select sum(e.sumflow) from KuyuAddPackage e where e.cardId =:cardId and e.starttime < now() and e.endtime >now() and e.status= 2 and e.type in (1,3)")
    BigDecimal findMonthlySumFlows(@Param("cardId")Integer cardId);

    /*
     * 查询沉默期处理日期错误的日志
     */
    @Query("from KuyuAddPackage e WHERE e.starttime BETWEEN '2018-05-08 15:00:00' AND now() ")
    List<KuyuAddPackage> findEndTimeError();

    @Modifying
    @Query("update KuyuAddPackage e set e.endtime = :endtime where e.id=:id")
    void flushEndTime(@Param("id") Integer id, @Param("endtime") Date endTime);
    
    //SELECT * FROM `kuyu_add_package` WHERE `card_id` = 527489 and `uid` =50 AND `type` = 2 AND `status` =2 AND `starttime` > '2018-06-01' AND `endtime` < '2018-07-01'
    @Query("select new KuyuAddPackage(ap.id,ap.card,ap.addtime,ap.starttime,ap.endtime,ap.cardId) FROM KuyuAddPackage ap where ap.type=2 AND ap.status=2 and ap.uid='50' AND starttime >= :starttime And endtime <=:endtime and ap.cardId =:id")
    List<KuyuAddPackage> findStopCorrelation(@Param("id") Integer id ,@Param("starttime") Date starttime, @Param("endtime") Date endtime);	
    
    @Query("SELECT SUM(e.sumflow),e.useflow FROM KuyuAddPackage e WHERE e.cardId =:cardId AND e.starttime < NOW() AND e.endtime >NOW() AND e.status= 2 AND e.type IN (1,3)")
    KuyuAddPackage findMonthlySumFlowsAndUseflow(@Param("cardId")Integer cardId);
    
    @Query("select new KuyuAddPackage(ap.starttime,ap.endtime,ap.type,ap.status) FROM KuyuAddPackage ap WHERE id=:id")
    KuyuAddPackage findAddPackgePropert(@Param("id")Integer id);
    
    @Modifying
    @Query("update KuyuAddPackage e set e.starttime = :starttime where e.id=:id")
    void updateStartTime(@Param("id")Integer id, @Param("starttime") Date starttime);
  
    
    @Modifying
    @Query("UPDATE KuyuAddPackage ap SET ap.status =3 WHERE ap.type IN(1,3) AND ap.status =2 AND ap.endtime BETWEEN :fisttime AND :endtime")
    void queryUnusePackageAndChangeStatus(@Param("fisttime")Date fisttime,@Param("endtime")Date endtime);
   
    
   /* @Modifying
    @Query("UPDATE KuyuAddPackage ap SET ap.type=1 WHERE ap.type=2 AND ap.starttime = :begintime")
    void queryToopenPackageAndChangeType(@Param("begintime")Date begintime);*/
    
    @Query("SELECT new KuyuAddPackage(ap.id,ap.cardId,ap.packageid) FROM KuyuAddPackage ap WHERE ap.type=2 AND ap.status =2 AND ap.starttime = :begintime")
    List<KuyuAddPackage> queryToopenPackage(@Param("begintime")Date begintime);
    
    @Modifying
    @Query("UPDATE KuyuAddPackage ap SET ap.type=1 WHERE ap.id = :id")
    void changeType(@Param("id")Integer id);
    
    @Query("SELECT new KuyuAddPackage(operatorName) FROM KuyuAddPackage")
    List<KuyuAddPackage> findOperatorName();
    
    
   /* @Query("SELECT new KuyuAddPackage(ap.uid,ap.card,ap.operatorName,ap.result,ap.remarks,ap.addtime,ap.starttime,ap.endtime,ap.cardId,ap.packageid,ap.type,ap.did,ap.source,ap.status,ap.serialNumber,ap.useflow,ap.sumflow,ap.pid,ap.finalprice,ap.profit) FROM KuyuAddPackage ap where ap.cardId=:cardid")
    List<KuyuAddPackage> findAllPackage(@Param("cardid")Integer cardid);
   */ 
    
   /* @Modifying
    @Query("INSERT INTO KuyuAddPackage(ADDTIME, beapiflow, card, cardId, did, endtime, finalprice, inuse, leftflow, operatorName, packageid, pid, pkg, profit, remarks, result, serialNumber, source, starttime, STATUS, sumflow, TYPE, uid, useflow) VALUES" + 
    		"(ADDTIME =:addtime, beapiflow =:beapiflow, card=:card,cardId=:card_id, did=:did, endtime=:endtime,finalprice= :finalprice, inuse=:inuse, leftflow=:leftflow, operatorName=:operator_name,packageid=:packageid, pid=:pid, pkg=:package,profit=:profit, remarks=:remarks,"
    		+ " result=:result,serialNumber=:serial_number, source=:source, starttime=:starttime, STATUS=:status, sumflow=:sumflow, TYPE=:type, uid=:uid, useflow=:useflow)")
    void savePackage(
    		@Param("addtime") Date addtime,
    		@Param("beapiflow") BigDecimal beapiflow,
    		@Param("card")	String card,
    		@Param("card_id") Integer cardId,
    		@Param("did")	Integer did,
    		@Param("finalprice") BigDecimal finalprice,
    		@Param("inuse") Integer inuse,
    		@Param("leftflow") BigDecimal  leftflow,
    		@Param("operator_name") String operatorName,
    		@Param("packageid") Integer packageid,
    		@Param("pid") Integer pid,
    		@Param("package")String pkg,
    		@Param("profit") BigDecimal profit,
    		@Param("remarks")String remarks,
    		@Param("result") String result,
    		@Param("serial_number")String serialNumber,
    		@Param("source")String source,
    		@Param("starttime") Date starttime,
    		@Param("status") Integer status,
    		@Param("sumflow")BigDecimal sumflow,
    		@Param("type")Integer type,
    		@Param("uid")Integer uid,
    		@Param("useflow")BigDecimal useflow

    		);*/
    
    
    @Query("SELECT new KuyuAddPackage(ap.card) FROM KuyuAddPackage ap WHERE ap.cardId =:cardId AND ap.packageid=:packageid")
    KuyuAddPackage findOrderByCardIdAndPackageid(@Param("cardId")Integer cardId,@Param("packageid")Integer packageid);
  
}
